﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic; 
using System.Data;
using System.Linq;
using Comm.Model;
using Comm.Utility;

namespace Comm.DAL
{
	public partial class CheckAddressDAO
	{
        #region 向数据库中添加一条记录 +int Insert(CheckAddress model)
        /// <summary>
        /// 向数据库中添加一条记录
        /// </summary>
        /// <param name="model">要添加的实体</param>
        /// <returns>插入数据的ID</returns>
        public int Insert(CheckAddress model)
        {
            #region SQL语句
            const string sql = @"
INSERT INTO [dbo].[CheckAddress] (
	[UserId]
	,[Address]
	,[statu]
	,[TelPhone]
	,[postCode]
	,[UserName]
)
VALUES (
	@UserId
	,@Address
	,@statu
	,@TelPhone
	,@postCode
	,@UserName
);select @@IDENTITY";
            #endregion
            var res = SqlHelper.ExecuteScalar(sql,
					new SqlParameter("@UserId", model.UserId),					
					new SqlParameter("@Address", model.Address),					
					new SqlParameter("@statu", model.statu),					
					new SqlParameter("@TelPhone", model.TelPhone),					
					new SqlParameter("@postCode", model.postCode),					
					new SqlParameter("@UserName", model.UserName)					
                );
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion

        #region 删除一条记录 +int Delete(int id)
        public int Delete(int id)
        {
            const string sql = "DELETE FROM [dbo].[CheckAddress] WHERE [Id] = @Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }
        #endregion

        #region 根据主键ID更新一条记录 +int Update(CheckAddress model)
        /// <summary>
        /// 根据主键ID更新一条记录
        /// </summary>
        /// <param name="model">更新后的实体</param>
        /// <returns>执行结果受影响行数</returns>
        public int Update(CheckAddress model)
        {
            #region SQL语句
            const string sql = @"
UPDATE [dbo].[CheckAddress]
SET 
	[UserId] = @UserId
	,[Address] = @Address
	,[statu] = @statu
	,[TelPhone] = @TelPhone
	,[postCode] = @postCode
	,[UserName] = @UserName
WHERE [Id] = @Id";
            #endregion
            return SqlHelper.ExecuteNonQuery(sql,
					new SqlParameter("@Id", model.Id),					
					new SqlParameter("@UserId", model.UserId),					
					new SqlParameter("@Address", model.Address),					
					new SqlParameter("@statu", model.statu),					
					new SqlParameter("@TelPhone", model.TelPhone),					
					new SqlParameter("@postCode", model.postCode),					
					new SqlParameter("@UserName", model.UserName)					
                );
        }

        public int Update1(int id) 
        {
            string sql1 = "update CheckAddress set statu='false'";
            SqlHelper.ExecuteNonQuery(sql1);
            string sql = "update CheckAddress set statu='true' where Id=@id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@id", id));
        }
        #endregion

        #region 分页查询一个集合 +IEnumerable<CheckAddress> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        /// <summary>
        /// 分页查询一个集合
        /// </summary>
        /// <param name="index">页码</param>
        /// <param name="size">页大小</param>
        /// <param name="wheres">条件匿名类</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isDesc">是否降序排序</param>
        /// <returns>实体集合</returns>
        public IEnumerable<CheckAddress> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("CheckAddress", new[] { "Id", "UserId", "Address", "statu", "TelPhone", "postCode", "UserName" }, index, size, whereBuilder.ToString(), orderField, isDesc);
            using (var reader = SqlHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        yield return SqlHelper.MapEntity<CheckAddress>(reader);
                    }
                }
            }
        }
        #endregion

        #region 查询单个模型实体 +CheckAddress QuerySingle(object wheres)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="wheres">条件匿名类</param>
        /// <returns>实体</returns>
        public CheckAddress QuerySingle(object wheres)
        {
            var list = QueryList(1, 1, wheres, null);
            return list != null && list.Any() ? list.FirstOrDefault() : null;
        }
        #endregion

        #region 查询单个模型实体 +CheckAddress QuerySingle(int id)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="id">key</param>
        /// <returns>实体</returns>
        public CheckAddress QuerySingle(int id)
        {
            const string sql = "SELECT TOP 1 [Id], [UserId], [Address], [statu], [TelPhone], [postCode], [UserName] FROM [dbo].[CheckAddress] WHERE [Id] = @Id";
            using (var reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return SqlHelper.MapEntity<CheckAddress>(reader);
                }
                else
                {
                    return null;
                }
            }
        }

        public CheckAddress QuerySingle1(int id)
        {
            const string sql = "SELECT TOP 1 [Id], [UserId], [Address], [statu], [TelPhone], [postCode], [UserName] FROM [dbo].[CheckAddress] WHERE [Id] = @Id";
            using (var reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return SqlHelper.MapEntity<CheckAddress>(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 查询条数 +int QueryCount(object wheres)
        /// <summary>
        /// 查询条数
        /// </summary>
        /// <param name="wheres">查询条件</param>
        /// <returns>条数</returns>
        public int QueryCount(object wheres)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("CheckAddress", new[] { "COUNT(1)" }, whereBuilder.ToString(), string.Empty);
            var res = SqlHelper.ExecuteScalar(sql, parameters.ToArray());
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion
		
				/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public CheckAddress GetModel(int Id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select Id, UserId, Address, statu, TelPhone, postCode, UserName  ");			
			strSql.Append("  from CheckAddress ");
			strSql.Append(" where Id=@Id");
						SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
			parameters[0].Value = Id;

			
			CheckAddress model=new CheckAddress();
			DataSet ds=SqlHelper.Query(strSql.ToString(),parameters);
			
			if(ds.Tables[0].Rows.Count>0)
			{
												if(ds.Tables[0].Rows[0]["Id"].ToString()!="")
				{
					model.Id=int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
				}
																																if(ds.Tables[0].Rows[0]["UserId"].ToString()!="")
				{
					model.UserId=int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
				}
																																				model.Address= ds.Tables[0].Rows[0]["Address"].ToString();
																																												if(ds.Tables[0].Rows[0]["statu"].ToString()!="")
				{
					if((ds.Tables[0].Rows[0]["statu"].ToString()=="1")||(ds.Tables[0].Rows[0]["statu"].ToString().ToLower()=="true"))
					{
					model.statu= true;
					}
					else
					{
					model.statu= false;
					}
				}
																if(ds.Tables[0].Rows[0]["TelPhone"].ToString()!="")
				{
					model.TelPhone=ds.Tables[0].Rows[0]["TelPhone"].ToString();
				}
																																if(ds.Tables[0].Rows[0]["postCode"].ToString()!="")
				{
					model.postCode=ds.Tables[0].Rows[0]["postCode"].ToString();
				}
																																				model.UserName= ds.Tables[0].Rows[0]["UserName"].ToString();
																										
				return model;
			}
			else
			{
				return null;
			}
		}
		
		
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select * ");
			strSql.Append(" FROM CheckAddress ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return SqlHelper.Query(strSql.ToString());
		}
		
		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" * ");
			strSql.Append(" FROM CheckAddress ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return SqlHelper.Query(strSql.ToString());
		}

        public IList<CheckAddress> GetAddressList(int userId) 
        {
            string sql = "SELECT [Id],  [Address], [statu], [TelPhone], [postCode], [UserName] FROM [dbo].[CheckAddress] WHERE [UserId] = @userId";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new SqlParameter("@userId", userId));
            IList<CheckAddress> list = null;
            if (dt.Rows.Count > 0) 
            {
                list = common.TBToList<CheckAddress>.Table2List(dt);
            }
            return list;
        }



	}
}